2 -- patch-comment-table.sql
4 -- T166732. Add a `comment` table and various columns (and temporary tables) to reference it.
6 CREATE SEQUENCE comment_comment_id_seq
;
7 CREATE TABLE &mw_prefix.
"COMMENT" (
8 comment_id
NUMBER NOT NULL,
9 comment_hash
NUMBER NOT NULL,
13 CREATE INDEX &mw_prefix.comment_hash
ON &mw_prefix.
"COMMENT" (comment_hash
);
15 CREATE TRIGGER &mw_prefix.comment_seq_trg
BEFORE INSERT ON &mw_prefix.
"COMMENT"
16 FOR EACH ROW WHEN (new.comment_id
IS NULL)
18 &mw_prefix.lastval_pkg.
setLastval(comment_comment_id_seq.nextval
, :new.comment_id
);
22 -- dummy row for FKs. Hash is intentionally wrong so CommentStore won't match it.
23 INSERT INTO &mw_prefix.
"COMMENT" (comment_hash
, comment_text
) VALUES (-1, '** dummy **');
26 CREATE TABLE &mw_prefix.
revision_comment_temp (
27 revcomment_rev
NUMBER NOT NULL,
28 revcomment_comment_id
NUMBER NOT NULL
30 ALTER TABLE &mw_prefix.revision_comment_temp
ADD CONSTRAINT &mw_prefix.revision_comment_temp_pk
PRIMARY KEY (revcomment_rev
, revcomment_comment_id
);
31 ALTER TABLE &mw_prefix.revision_comment_temp
ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk1
FOREIGN KEY (revcomment_rev
) REFERENCES &mw_prefix.
revision(rev_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
32 ALTER TABLE &mw_prefix.revision_comment_temp
ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk2
FOREIGN KEY (revcomment_comment_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
33 CREATE UNIQUE INDEX &mw_prefix.revcomment_rev
ON &mw_prefix.
revision_comment_temp (revcomment_rev
);
36 CREATE TABLE &mw_prefix.
image_comment_temp (
37 imgcomment_name
VARCHAR2(255) NOT NULL,
38 imgcomment_description_id
NUMBER NOT NULL
40 ALTER TABLE &mw_prefix.image_comment_temp
ADD CONSTRAINT &mw_prefix.image_comment_temp_pk
PRIMARY KEY (imgcomment_name
, imgcomment_description_id
);
41 ALTER TABLE &mw_prefix.image_comment_temp
ADD CONSTRAINT &mw_prefix.image_comment_temp_fk1
FOREIGN KEY (imgcomment_name
) REFERENCES &mw_prefix.
image(img_name
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
42 ALTER TABLE &mw_prefix.image_comment_temp
ADD CONSTRAINT &mw_prefix.image_comment_temp_fk2
FOREIGN KEY (imgcomment_description_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
43 CREATE UNIQUE INDEX &mw_prefix.imgcomment_name
ON &mw_prefix.
image_comment_temp (imgcomment_name
);
46 ALTER TABLE &mw_prefix.archive
ADD ( ar_comment_id
NUMBER DEFAULT 0 NOT NULL );
47 ALTER TABLE &mw_prefix.archive
ADD CONSTRAINT &mw_prefix.archive_fk2
FOREIGN KEY (ar_comment_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
49 ALTER TABLE &mw_prefix.ipblocks
MODIFY ( ipb_reason
NULL );
50 ALTER TABLE &mw_prefix.ipblocks
ADD ( ipb_reason_id
NUMBER DEFAULT 0 NOT NULL );
51 ALTER TABLE &mw_prefix.ipblocks
ADD CONSTRAINT &mw_prefix.ipblocks_fk3
FOREIGN KEY (ipb_reason_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
53 ALTER TABLE &mw_prefix.oldimage
ADD ( oi_description_id
NUMBER DEFAULT 0 NOT NULL );
54 ALTER TABLE &mw_prefix.oldimage
ADD CONSTRAINT &mw_prefix.oldimage_fk3
FOREIGN KEY (oi_description_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
56 ALTER TABLE &mw_prefix.filearchive
ADD ( fa_deleted_reason_id
NUMBER DEFAULT 0 NOT NULL );
57 ALTER TABLE &mw_prefix.filearchive
ADD ( fa_description_id
NUMBER DEFAULT 0 NOT NULL );
58 ALTER TABLE &mw_prefix.filearchive
ADD CONSTRAINT &mw_prefix.filearchive_fk3
FOREIGN KEY (fa_deleted_reason_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
59 ALTER TABLE &mw_prefix.filearchive
ADD CONSTRAINT &mw_prefix.filearchive_fk4
FOREIGN KEY (fa_description_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
61 ALTER TABLE &mw_prefix.recentchanges
ADD ( rc_comment_id
NUMBER DEFAULT 0 NOT NULL );
62 ALTER TABLE &mw_prefix.recentchanges
ADD CONSTRAINT &mw_prefix.recentchanges_fk3
FOREIGN KEY (rc_comment_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
64 ALTER TABLE &mw_prefix.logging
ADD ( log_comment_id
NUMBER DEFAULT 0 NOT NULL );
65 ALTER TABLE &mw_prefix.logging
ADD CONSTRAINT &mw_prefix.logging_fk2
FOREIGN KEY (log_comment_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
67 ALTER TABLE &mw_prefix.protected_titles
ADD ( pt_reason_id
NUMBER DEFAULT 0 NOT NULL );
68 ALTER TABLE &mw_prefix.protected_titles
ADD CONSTRAINT &mw_prefix.protected_titles_fk1
FOREIGN KEY (pt_reason_id
) REFERENCES &mw_prefix.
"COMMENT"(comment_id
) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;